/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.report;

import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.vo.FieldDataVO;
import cn.com.jsepc.rep.vo.RepDataVO;
import cn.com.jsepc.rep.vo.RepDemensionVO;
import com.jarp.db.DAO;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * 
 * @author yuan
 */
public class GetHandmadeReport {

	public String getHandmade() {
		KeyCalc cal = new KeyCalc();
		String res = "";
		DAO dao = new DAO();
		int totalCount=0;        //检索手工填报的指标的数量
		String SQL = "SELECT t.REPID,t.REFFACT FROM tb_report t WHERE t.SOURCETYPE=2 AND t.REFFACT is NOT NULL";
	    try {
			totalCount = dao.getDataSetCount(ConstUtil.DBNAME, SQL);
			System.out.println("totalCount:"+totalCount);
		} catch (ServletException e) {
			System.out.println("没有指标数据！");
			return "no result";
		}
		int totalPage = totalCount % 1000 > 0 ? totalCount / 1000 + 1
				: totalCount / 1000;
		for (int nowPage = 1; nowPage <= totalPage; nowPage++) {
			Vector v =null;
		    try {
				v = dao.getDataSet(ConstUtil.DBNAME, SQL, nowPage, 1000);
				
			} catch (ServletException e) {
				continue;
			}
			if (v != null) {
				if(v.size()<=0){
					continue;
				}
				for (int i = 0; i < v.size(); i++) {
					Hashtable hash = (Hashtable) v.get(i);
					String time_col=null;  //时间字段名
					String reffact = (String) hash.get("REFFACT"); // 事实表表名
					String repid = (String) hash.get("REPID"); // 指标编码
					String[] DemRow = null; // 维度列名 数组
					String get_Tim_unit = "SELECT c.FACDIMCOL FROM "
							+ "tb_report a"
							+ " LEFT JOIN tb_rep_demension b on a.REPID=b.REPID "
							+ "LEFT JOIN tb_demension c on c.FACDIMCOL=b.FACTDIMCOL"
							+ " WHERE a.REPID=" + repid + " AND c.DIMTYPE=2";

					try {
						time_col = dao.getDataValue(ConstUtil.DBNAME,
								get_Tim_unit);
					} catch (ServletException e) {
						continue;
					} 
					RepDataVO repVo = getRepDemension(repid);
					if (repVo == null) {
						continue;
					}
					ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
					if (repDemLst == null || repDemLst.size() == 0) {
						continue;
					}
					DemRow = new String[repDemLst.size()];
					for (int j = 0; j < repDemLst.size(); j++) {
						RepDemensionVO repDemVO = repDemLst.get(j);
						String dimidcol = repDemVO.getFactdimcol(); // ORG_ID
						DemRow[j] = dimidcol;
					}
					int irows=0;
					 try {
						irows = dao.getDataSetCount(ConstUtil.DBNAME,
										"select * from "
												+ reffact
												+ " WHERE repval is NOT NULL OR sumval is NOT NULL");
					} catch (ServletException e) {
						continue;
					}
					int iPage = irows % 1000 > 0 ? irows / 1000 + 1
							: irows / 1000;
					for (int page = 1; page <= iPage; page++) {
						Vector thisResult=null;
						String thisPage = "select * from "
								+ reffact
								+ " WHERE repval is NOT NULL OR sumval is NOT NULL";
						try {
							thisResult = dao.getDataSet(ConstUtil.DBNAME,
									thisPage, page, 1000);
						} catch (ServletException e) {
							continue;
						}
						
						boolean b = cal.CalcValues(reffact, time_col, DemRow,
								thisResult);
						if (b == true) {
							System.out.println("事实表" + reffact + "数据更新成功");
						} else {
							System.out.println("事实表" + reffact + "数据更新失败");
						}
						/*
						 * 向后台传输数据 所需参数： getParam 结果集 time_col:时间字段名 DemRow维度列名
						 * 数组 事实表表名 tablename
						 */
					}
				}
			}
		}
		return res;
	}
	
	
	public void getReport(String str){
		String [] report=str.split(",");
		String res=this.getHandmade(report);
		System.out.println(res);
	}

	public String getHandmade(String facttable) {
		String res = "";
		KeyCalc cal = new KeyCalc();
		DAO dao = new DAO();
		String getRepId = "SELECT t.REPID from tb_report t WHERE t.REFFACT='"
				+ facttable + "'";
		String repid = "";
		String[] DemRow = null; // 维度列名 数组
		try {
			repid = dao.getDataValue(ConstUtil.DBNAME, getRepId);
			String get_Tim_unit = "SELECT c.FACDIMCOL FROM " + "tb_report a"
					+ " LEFT JOIN tb_rep_demension b on a.REPID=b.REPID "
					+ "LEFT JOIN tb_demension c on c.FACDIMCOL=b.FACTDIMCOL"
					+ " WHERE a.REPID=" + repid + " AND c.DIMTYPE=2";

			String time_col = dao.getDataValue(ConstUtil.DBNAME, get_Tim_unit); // 时间字段名
			RepDataVO repVo = getRepDemension(repid);
			if (repVo == null) {
				return "false";
			}
			ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
			if (repDemLst == null || repDemLst.size() == 0) {
				return null;
			}
			DemRow = new String[repDemLst.size()];
			for (int j = 0; j < repDemLst.size(); j++) {
				RepDemensionVO repDemVO = repDemLst.get(j);
				String dimidcol = repDemVO.getFactdimcol(); // ORG_ID
				DemRow[j] = dimidcol;
			}
			int irows = dao.getDataSetCount(ConstUtil.DBNAME, "select * from "
					+ facttable
					+ " WHERE repval is NOT NULL OR sumval is NOT NULL");
			int iPage = irows % 1000 > 0 ? irows / 1000 + 1 : irows / 1000;
			for (int page = 1; page <= iPage; page++) {
				String thisPage = "select * from " + facttable
						+ " WHERE repval is NOT NULL OR sumval is NOT NULL";
				Vector thisResult = dao.getDataSet(ConstUtil.DBNAME, thisPage,
						page, 1000);
				for (int i2 = 0; i2 < DemRow.length; i2++) {
					System.out.print(DemRow[i2] + ",");
				}
				boolean b = cal.CalcValues(facttable, time_col, DemRow,
						thisResult);
				if (b == true) {
					// System.out.println("事实表"+facttable+"数据更新成功");
				} else {
					System.out.println("事实表" + facttable + "数据更新失败");
				}
				/*
				 * 向后台传输数据 所需参数： getParam 结果集 time_col:时间字段名 DemRow维度列名 数组 事实表表名
				 * tablename
				 */
			}
		} catch (ServletException ex) {
			Logger.getLogger(GetHandmadeReport.class.getName()).log(
					Level.SEVERE, null, ex);
		}
		return res;
	}

	public String getHandmade(String[] facttable) {
		String res = "";
		KeyCalc cal = new KeyCalc();
		DAO dao = new DAO();
		boolean b = true; // 判断数据有没有全部更新成功
		for (int tablecount = 0; tablecount < facttable.length; tablecount++) {
			String getRepId = "SELECT t.REPID from tb_report t WHERE t.REFFACT='"
					+ facttable[tablecount] + "'";
			String repid = "";
			String[] DemRow = null; // 维度列名 数组
			try {
				repid = dao.getDataValue(ConstUtil.DBNAME, getRepId);
				String get_Tim_unit = "SELECT c.FACDIMCOL FROM "
						+ "tb_report a"
						+ " LEFT JOIN tb_rep_demension b on a.REPID=b.REPID "
						+ "LEFT JOIN tb_demension c on c.FACDIMCOL=b.FACTDIMCOL"
						+ " WHERE a.REPID=" + repid + " AND c.DIMTYPE=2";

				String time_col = dao.getDataValue(ConstUtil.DBNAME,
						get_Tim_unit); // 时间字段名
				RepDataVO repVo = getRepDemension(repid);
				if (repVo == null) {
					System.out.println("无法获取维度列名");
					break;
				}
				ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
				if (repDemLst == null || repDemLst.size() == 0) {
					return null;
				}
				DemRow = new String[repDemLst.size()];
				for (int j = 0; j < repDemLst.size(); j++) {
					RepDemensionVO repDemVO = repDemLst.get(j);
					String dimidcol = repDemVO.getFactdimcol(); // ORG_ID
					DemRow[j] = dimidcol;
				}
				int irows = dao
						.getDataSetCount(
								ConstUtil.DBNAME,
								"select * from "
										+ facttable[tablecount]
										+ " WHERE repval is NOT NULL OR sumval is NOT NULL");
				int iPage = irows % 1000 > 0 ? irows / 1000 + 1 : irows / 1000;
				for (int page = 1; page <= iPage; page++) {
					String thisPage = "select * from " + facttable[tablecount]
							+ " WHERE repval is NOT NULL OR sumval is NOT NULL";
					Vector thisResult = dao.getDataSet(ConstUtil.DBNAME,
							thisPage, page, 1000);
					
					boolean result = cal.CalcValues(facttable[tablecount],
							time_col, DemRow, thisResult);
					if (result == false) {
						b = false;
						System.out.println("事实表" + facttable[tablecount]
								+ "数据更新失败");
					}else{
						System.out.println("事实表" + facttable[tablecount]
						     								+ "数据更新成功");
					}
					/*
					 * 向后台传输数据 所需参数： getParam 结果集 time_col:时间字段名 DemRow维度列名 数组
					 * 事实表表名 tablename
					 */
				}

			} catch (ServletException ex) {
				Logger.getLogger(GetHandmadeReport.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		if (b == true) {
			res = "ok";
		} else {
			res = "wrong";
		}
		return res;
	}

	public String getSelections(String str) {
		String res = "ok";
		String[] facttable = str.split(",");
		KeyCalc cal = new KeyCalc();
		DAO dao = new DAO();
		for (int tablecount = 0; tablecount < facttable.length; tablecount++) {
			String getRepId = "SELECT t.REPID from tb_report t WHERE t.REFFACT='"
					+ facttable[tablecount] + "'";
			System.out.println(getRepId);
			String repid = "";
			String time_col="";
			String[] DemRow = null; // 维度列名 数组
	
				try {
					repid = dao.getDataValue(ConstUtil.DBNAME, getRepId);
				} catch (ServletException e) {
					continue;
				}
				System.out.println("repid:" + repid);
				String get_Tim_unit = "SELECT c.FACDIMCOL FROM "
						+ "tb_report a"
						+ " LEFT JOIN tb_rep_demension b on a.REPID=b.REPID "
						+ "LEFT JOIN tb_demension c on c.FACDIMCOL=b.FACTDIMCOL"
						+ " WHERE a.REPID=" + repid + " AND c.DIMTYPE=2";

				try {
					time_col= dao.getDataValue(ConstUtil.DBNAME,
							get_Tim_unit);// 时间字段名
				} catch (ServletException e) {
					continue;
				} 
				RepDataVO repVo = getRepDemension(repid);
				if (repVo == null) {
					System.out.println("无法获取维度列名");
					continue;
				}
				ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
				if (repDemLst == null || repDemLst.size() == 0) {
					continue;
				}
				DemRow = new String[repDemLst.size()];
				for (int j = 0; j < repDemLst.size(); j++) {
					RepDemensionVO repDemVO = repDemLst.get(j);
					String dimidcol = repDemVO.getFactdimcol(); // ORG_ID
					DemRow[j] = dimidcol;
				}
				int irows=0;
				 try {
					irows = dao
							.getDataSetCount(
									ConstUtil.DBNAME,
									"select * from "
											+ facttable[tablecount]
											+ " WHERE repval is NOT NULL OR sumval is NOT NULL");
				} catch (ServletException e) {
					continue;
				}
				int iPage = irows % 1000 > 0 ? irows / 1000 + 1 : irows / 1000;
				for (int page = 1; page <= iPage; page++) {
					String thisPage = "select * from " + facttable[tablecount]
							+ " WHERE repval is NOT NULL OR sumval is NOT NULL";
					Vector thisResult=new Vector();
					try {
						thisResult = dao.getDataSet(ConstUtil.DBNAME,
								thisPage, page, 1000);
						if(thisResult.size()<=0){
							continue;
						}
					} catch (ServletException e) {
						continue;
					}
					for (int i2 = 0; i2 < DemRow.length; i2++) {
						System.out.print(DemRow[i2] + ",");
					}
					boolean result = cal.CalcValues(facttable[tablecount],
							time_col, DemRow, thisResult);
					if (result == false) {
						res = "wrong";
						System.out.println("事实表" + facttable[tablecount]
								+ "数据更新失败");
					}
					/*
					 * 向后台传输数据 所需参数： getParam 结果集 time_col:时间字段名 DemRow维度列名 数组
					 * 事实表表名 tablename
					 */
				}

		}
		return res;
	}

	/**
	 * 加载指标对象
	 * 
	 * @param repid
	 * @return
	 */
	public RepDataVO getRepDemension(String repid) {
		RepDataVO repVO = new RepDataVO();
		DAO dao = new DAO();
		try {
			String sql = "select * from ( select * from tb_report where repid="
					+ repid
					+ " and ISVALID=1 ) a"
					+ " left join tb_rep_demension b on b.repid=a.repid"
					+ " left join tb_demension c on c.demid=b.dimid order by b.dimorder";
			Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
			if (v != null) {
				ArrayList<RepDemensionVO> repDemLst = new ArrayList<RepDemensionVO>();
				for (int i = 0; i < v.size(); i++) {
					Hashtable hashRows = (Hashtable) v.get(i);
					RepDemensionVO repDemVO = new RepDemensionVO();
					repDemVO.setId((String) hashRows.get("ID"));
					repDemVO.setRepid((String) hashRows.get("REPID"));
					repDemVO.setDimid((String) hashRows.get("DIMID"));
					repDemVO.setHirecdesc((String) hashRows.get("HIRECDESC"));
					repDemVO.setDemname((String) hashRows.get("DEMNAME"));
					repDemVO.setFactdimcol(((String) hashRows.get("FACDIMCOL"))
							.toUpperCase());
					repDemVO.setAvailsname((String) hashRows.get("AVAILSNAME"));
					repDemVO.setDimorder((String) hashRows.get("DIMORDER"));
					repDemVO.setDemcode((String) hashRows.get("DEMCODE"));
					repDemVO.setDimtable((String) hashRows.get("DIMTABLE"));
					repDemVO.setIdcol(((String) hashRows.get("IDCOL"))
							.toUpperCase());
					repDemVO.setNamecol(((String) hashRows.get("NAMECOL"))
							.toUpperCase());
					repDemVO.setParentcol(((String) hashRows.get("PARENTCOL"))
							.toUpperCase());
					repDemVO.setCodecol(((String) hashRows.get("CODECOL"))
							.toUpperCase());
					repDemVO.setDemtype((String) hashRows.get("DIMTYPE"));
					repDemVO.setIsvalid((String) hashRows.get("ISVALID"));
					repDemVO.setLevelcol((String) hashRows.get("LEVELCOL"));
					repDemLst.add(repDemVO);
					if (i == v.size() - 1) {
						repVO.setRepid((String) hashRows.get("REPID"));
						repVO.setRepcode((String) hashRows.get("REPCODE"));
						repVO.setRepname((String) hashRows.get("REPNAME"));
						repVO.setCataid((String) hashRows.get("CATAID"));
						repVO.setRepdesc((String) hashRows.get("REPDESC"));
						repVO.setDesc((String) hashRows.get("CALDESC"));
						repVO.setDataprec((String) hashRows.get("DATAPREC"));
						repVO.setDataunit((String) hashRows.get("DATAUNIT"));
						repVO
								.setDepartment((String) hashRows
										.get("DEPARTMENT"));
						repVO
								.setSourcetype((String) hashRows
										.get("SOURCETYPE"));
						repVO.setDatatype((String) hashRows.get("DATATYPE"));
						repVO
								.setUpdatetime((String) hashRows
										.get("UPDATETIME"));
						repVO.setReffact((String) hashRows.get("REFFACT"));
						repVO.setRepvaluecol(((String) hashRows
								.get("REPVALUECOL")).toUpperCase());
						repVO.setIssum((String) hashRows.get("ISSUM"));
						repVO.setIsvalid((String) hashRows.get("ISVALID"));
						repVO.setRepDemLst(repDemLst);
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			System.out.println("FillDataDAO getFillGrid Exception.");
		}
		return repVO;
	}
}
